package it.orma.iridehr.repository;

import it.orma.iridehr.controllers.ricerca.NewsForm;
import it.orma.iridehr.entities.DatiGenerali;
import it.orma.iridehr.entities.News;
import it.orma.iridehr.entities.Opportunita;
import it.orma.iridehr.entities.OpportunitaCandidato;
import it.orma.iridehr.entities.SchedaCandidato;
import it.orma.iridehr.entities.StudiCompiuti;
import it.orma.iridehr.entities.TemplateEmail;
import it.orma.iridehr.entities.TitoloDiStudio;

import java.math.BigInteger;
import java.util.ArrayList;
import java.sql.Date;
import java.sql.Timestamp;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

@Repository
public class NewsRepository {

	@PersistenceContext
	private EntityManager entityManager;

	public News caricaNews(Long idNews){
		String hql = "select s from "+News.class.getName()+" as s where s.idNews = ?";
		News news = (News) entityManager.createQuery(hql).setParameter(1, idNews).getSingleResult();
		entityManager.detach(news);
		return news;
		
	}
	
	
	public List<News> caricaNews(int page){
		
		String nativeSql = "SELECT *"+
				  " FROM (SELECT n.email,"+
			      "         n.id_opportunita,"+
				  "			op.codice,"+	
			      "         n.DT_CREAZIONE,"+
			      "         n.mail_inviata,"+
			      "         n.gestito," +
			      "			n.id_news,"+
			      "         (SELECT count(*)"+
			      "           FROM opportunita_candidato d, dati_generali s"+
			      "          WHERE     d.id_candidato = s.fk_scheda_candidato"+
			      "                 AND s.ds_mail1 = n.email)"+
			      "            hasOpps,"+
			      "         (SELECT count(*)"+
			      "            FROM dati_generali"+
			      "          WHERE ds_mail1 = n.email)"+
			      "           existing,"+
			      "         (SELECT fk_scheda_candidato"+
			      "            FROM dati_generali"+
			      "          WHERE ds_mail1 = n.email" +
			      "				limit 0,1)"+
			      "           id_candidato,"+
			      "			op.progressivo "+	
			      " FROM news_candidati n left join opportunita op"+
                  " ON n.id_opportunita = op.id_opportunita"+
			      "   WHERE n.visibile = 'S'	"+
			      "  ORDER BY n.dt_creazione ASC, n.mail_inviata ASC) view "+
			      " LIMIT "+(page-1)*20+", 20";
				
		List<News> list = new ArrayList<News>();
		List<Object[]> result = entityManager.createNativeQuery(nativeSql).getResultList();
		Iterator<Object[]> resultIt = result.iterator();
		while(resultIt.hasNext()){
			Object[] record = resultIt.next();
			News news = new News();
			news.setEmail((String) record[0]);
			Opportunita op = new Opportunita();
			if (record[1]!=null){
				op.setIdOpportunita(((BigInteger)record[1]).longValue());
				op.setCodice((String)record[2]);
				op.setProgressivo(((BigInteger)record[10]).longValue());
				news.setOpportunita(op);
			}
			news.setDataCreazione(new Date(((Timestamp)record[3]).getTime()));
			news.setMailInviata((String) record[4]);
			news.setGestito((String) record[5]);
			news.setIdNews(((BigInteger)record[6]).longValue());
			news.setHasOpportunita(((BigInteger)record[7]).intValue()>0);
			news.setExisting(((BigInteger)record[8]).intValue()>0);
			BigInteger idCandidato = (BigInteger)record[9];
			if ( idCandidato!=null)
				news.setIdCandidato(idCandidato.longValue());
			list.add(news);
		}
		
		entityManager.detach(result);
		entityManager.detach(list);
		
		return list;
	}
	
	public Integer countNews(){
		
		String hql = "select count(*) from "+News.class.getCanonicalName()+" as n where n.visibile = 'S'";
		Query query = entityManager.createQuery(hql);
		return (new Long((Long) query.getSingleResult()).intValue());
	}

	public void associaOpportunita(Long idCandidato,Long idNews) {
		
		String hql ="select n from "+News.class.getName()+" as n where n.idNews=?";
		News n = (News) entityManager.createQuery(hql).setParameter(1, idNews).getSingleResult();
		if(n.getOpportunita()!=null){
			OpportunitaCandidato oc = new OpportunitaCandidato();
			oc.setOpportunita(n.getOpportunita());
			SchedaCandidato sc = new SchedaCandidato();
			sc.setIdSchedaCandidato(idCandidato);
			oc.setSchedaCandidato(sc);
			entityManager.persist(oc);
			entityManager.flush();
		}
		entityManager.detach(n);
		
	}

	public void gestisciNews(Long idNews) {
		String hql ="select n from "+News.class.getName()+" as n where n.idNews=?";
		News n = (News) entityManager.createQuery(hql).setParameter(1, idNews).getSingleResult();
		n.setGestito("S");
		n.setMailInviata("N");
		entityManager.merge(n);	
		entityManager.flush();
		entityManager.detach(n);
	}
	
	@Transactional
	public void inviaEmail(Long idNews) {
		String hql ="select n from "+News.class.getName()+" as n where n.idNews=?";
		News n = (News) entityManager.createQuery(hql).setParameter(1, idNews).getSingleResult();
		n.setMailInviata("S");
		entityManager.merge(n);	
		entityManager.flush();
		entityManager.detach(n);
	}
	
	@Transactional
	public void nascondiNews(Long idNews) {
		String hql ="select n from "+News.class.getName()+" as n where n.idNews=?";
		News n = (News) entityManager.createQuery(hql).setParameter(1, idNews).getSingleResult();
		n.setVisibile("N");
		entityManager.merge(n);	
		entityManager.flush();
		entityManager.detach(n);
	}
	
	public void insertNews(News news) {
		entityManager.persist(news);
		entityManager.flush();
		entityManager.detach(news);		
	}
	
	@Transactional
	public void insertNewsNoTx(News news) {
		entityManager.persist(news);
		entityManager.flush();
		entityManager.detach(news);		
	}

	public String caricaTemplate(String tipoTemplate) {
		String hql ="select n from "+TemplateEmail.class.getName()+" as n where n.tipo = ?";
		TemplateEmail n = (TemplateEmail) entityManager.createQuery(hql).setParameter(1, tipoTemplate).getSingleResult();
		entityManager.detach(n);
		return n.getContenuto();	
	}
		
		
}
